package com.egao.common.module.aolan.service;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.DbUtil;
import cn.hutool.db.sql.SqlExecutor;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.egao.common.core.Constants;
import com.egao.common.core.web.JsonResult;
import com.egao.common.module.aolan.entity.AoLan;
import com.egao.common.module.system.constants.LoggerConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 连结奥兰数据库
 *
 * @author lym
 */
@Service
public class AoLanSqlService {

    @Value("${ao-lan.url}")
    private String url;

    @Value("${ao-lan.username}")
    private String username;

    @Value("${ao-lan.password}")
    private String password;

    private Logger logger = LoggerFactory.getLogger(AoLanSqlService.class.getName());

    private final String INSERT_COLUMNS = "st_sttb(xdm, bjhm, xh, xm, byqxfldm, datgqxdm, byqxdm, bdzqflbdm, dwmc, dwdm, dwlsbmdm, dwszddm, dwxzdm, jyzkdm, jylbdm, wjylbdm, kzx1, kzx5, dwzzjg, dwhydm, gzzwlbdm, sjdwszddm, hbzdcydm, pqbdzh, pqsj, shyj, dwdz, dwyzbm, dwlxr, dwlxfs, daclqk, dazddz, dazdyb, dazddh, hkqx, hqtjdm, yxdjdm, qymyddm, gzzyxgdm, rzppdm, hdodsldm, jygzmyddm, ck_xys, ck_sydtg, ck_sbbtg, ck_srctg, ck_whsrctg, qtrcjgjsh, ck_cgzm, ck_sxcl, ck_jyzm, czsj, fdysp, fdy, fdyspsj, yxsp, yxspr, yxspsj, xjsp, xjspr, xjspsj, zydm, hkqzlxdm, hkqzszddm, lqyxdm)";

    private DruidPooledConnection connection(Integer year) {
        try {
            DruidDataSource db = new DruidDataSource();
            db.setUrl(url + year);
            db.setUsername(username);
            db.setPassword(password);
            db.setConnectionErrorRetryAttempts(2);
            db.setBreakAfterAcquireFailure(true);
            return db.getConnection();
        } catch (SQLException e) {
            logger.error(LoggerConstants.LOGGER_ERROR_AO_LAN_SQL_CONN, e);
        }
        return null;
    }

    /**
     * 插入一条学生毕业去向信息
     *
     * @param aoLan 需要插入的毕业去向信息
     */
    public JsonResult insert(AoLan aoLan) {
        if (null == aoLan) {
            return JsonResult.error("数据异常");
        }

        if (null == aoLan.getYearId()) {
            return JsonResult.error("数据年份异常");
        }

        Connection conn = connection(aoLan.getYearId());
        if (null == conn) {
            return JsonResult.error("数据连接异常");
        }

        JsonResult jsonResult = delete(conn, aoLan.getXh());
        if (Constants.RESULT_ERROR_CODE == jsonResult.getCode()) {
            return jsonResult;
        }

        int state = 0;
        String msg = "数据未插入到奥兰";
        try {
            String data = aoLanToString(aoLan);
            String sql = "INSERT INTO " + INSERT_COLUMNS + " VALUES " + data;
            System.out.println("SQL：" + sql);
            state = SqlExecutor.execute(conn, sql);
        } catch (SQLException e) {
            msg = "数据插入到奥兰时异常：" + e.getMessage();
            logger.error(LoggerConstants.LOGGER_ERROR_AO_LAN_SQL_INSERT, e);
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            if (1 == state) {
                return JsonResult.ok();
            } else {
                return JsonResult.error(msg);
            }
        }
    }

    /**
     * 查询学号是否存在
     * 异常返回存在就不做后续操作 不存在会填充数据
     * @param year 毕业年份
     * @param xh 学号
     * @return true存在 false不存在
     */
    public boolean select(Integer year, String xh) {
        Connection conn = connection(year);
        if (null == conn) {
            return true;
        }

        int i = 0;
        try {
            i = SqlExecutor.execute(conn, "SELECT * FROM st_sttb WHERE xh = '" + xh + "'");
        } catch (SQLException e) {
            logger.error(LoggerConstants.LOGGER_ERROR_AO_LAN_SQL_DELETE_XH, e);
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (i == 0) {
            return false;
        }

        return true;
    }

    /**
     * 删除某个学生毕业去向
     *
     * @param conn sql连接
     * @param xh   学号
     */
    private JsonResult delete(Connection conn, String xh) {
        if (StrUtil.isBlank(xh)) {
            return JsonResult.error("学号异常");
        }

        try {
            SqlExecutor.execute(conn, "DELETE FROM st_sttb WHERE xh = " + xh);
            return JsonResult.ok();
        } catch (SQLException e) {
            DbUtil.close(conn);
            logger.error(LoggerConstants.LOGGER_ERROR_AO_LAN_SQL_DELETE, e);
            return JsonResult.error("数据清理异常：" + e.getMessage());
        }
    }

    /**
     * 删除某个学生的毕业去向
     *
     * @param year 毕业年份
     * @param xh   学号
     */
    public void delete(Integer year, String xh) {
        if (null == year) {
            return;
        }
        if (StrUtil.isBlank(xh)) {
            return;
        }

        Connection conn = connection(year);
        if (null == conn) {
            return;
        }

        try {
            SqlExecutor.execute(conn, "DELETE FROM st_sttb WHERE xh = '" + xh + "'");
        } catch (SQLException e) {
            logger.error(LoggerConstants.LOGGER_ERROR_AO_LAN_SQL_DELETE_XH, e);
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public JsonResult insertBatch(Integer year, List<AoLan> list) {
        int count = list.size();
        int success = 0;
        int error = 0;
        String msg = "";
        String msg1 = "";
        Connection conn = connection(year);
        if (null == conn) {
            return JsonResult.error("数据连接异常");
        }

        try {
            SqlExecutor.execute(conn, "DELETE FROM st_sttb");
        } catch (SQLException e) {
            return JsonResult.error("删除全部异常");
        }

        List<String> errorResult = new ArrayList<>();

        for (AoLan aoLan : list) {
            String data = aoLanToString(aoLan);
            String sql = "INSERT INTO " + INSERT_COLUMNS + " VALUES " + data;
            try {
                int state = SqlExecutor.execute(conn, sql);
                if (state == 1) {
                    success++;
                } else {
                    error++;
                    msg += aoLan.getXh() + ",";
                    System.out.println("ELSE ERROR：" + aoLan.getXh());
                }
            } catch (SQLException e) {
                error++;
                msg1 += aoLan.getXh() + ",";
                errorResult.add(sql);
                System.out.println("ERROR：" + aoLan.getXh() + "：" + e.getMessage());
            }
        }
        try {
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return JsonResult.ok( "总：" + count + "/成功：" + success + "/失败：" + error + "/ELSE MSG：" + msg + "/MSG" + msg1)
                .put("sqls", errorResult);
    }

    private String aoLanToString(AoLan aoLan) {
        String aoLanStr = "";
        aoLanStr = "(" + aoLan.getXdm() + "," +
                aoLan.getBjhm() + "," +
                aoLan.getXh() + "," +
                aoLan.getXm() + "," +
                aoLan.getByqxfldm() + "," +
                aoLan.getDatgqxdm() + "," +
                aoLan.getByqxdm() + "," +
                aoLan.getBdzqflbdm() + "," +
                aoLan.getDwmc() + "," +
                aoLan.getDwdm() + "," +
                aoLan.getDwlsbmdm() + "," +
                aoLan.getDwszddm() + "," +
                aoLan.getDwxzdm() + "," +
                aoLan.getJyzkdm() + "," +
                aoLan.getJylbdm() + "," +
                aoLan.getWjylbdm() + "," +
                aoLan.getKzx1() + "," +
                aoLan.getKzx5() + "," +
                aoLan.getDwzzjg() + "," +
                aoLan.getDwhydm() + "," +
                aoLan.getGzzwlbdm() + "," +
                aoLan.getSjdwszddm() + "," +
                aoLan.getHbzdcydm() + "," +
                aoLan.getPqbdzh() + "," +
                aoLan.getPqsj() + "," +
                aoLan.getShyj() + "," +
                aoLan.getDwdz() + "," +
                aoLan.getDwyzbm() + "," +
                aoLan.getDwlxr() + "," +
                aoLan.getDwlxfs() + "," +
                aoLan.getDaclqk() + "," +
                aoLan.getDazddz() + "," +
                aoLan.getDazdyb() + "," +
                aoLan.getDazddh() + "," +
                aoLan.getHkqx() + "," +
                aoLan.getHqtjdm() + "," +
                aoLan.getYxdjdm() + "," +
                aoLan.getQymyddm() + "," +
                aoLan.getGzzyxgdm() + "," +
                aoLan.getRzppdm() + "," +
                aoLan.getHdodsldm() + "," +
                aoLan.getJygzmyddm() + "," +
                aoLan.getCkXys() + "," +
                aoLan.getCkSydtg() + "," +
                aoLan.getCkSbbtg() + "," +
                aoLan.getCkSrctg() + "," +
                aoLan.getCkWhsrctg() + "," +
                aoLan.getQtrcjgjsh() + "," +
                aoLan.getCkCgzm() + "," +
                aoLan.getCkSxcl() + "," +
                aoLan.getCkJyzm() + "," +
                aoLan.getCzsj() + "," +
                aoLan.getFdysp() + "," +
                aoLan.getFdy() + "," +
                aoLan.getFdyspsj() + "," +
                aoLan.getYxsp() + "," +
                aoLan.getYxspr() + "," +
                aoLan.getYxspsj() + "," +
                aoLan.getXjsp() + "," +
                aoLan.getXjspr() + "," +
                aoLan.getXjspsj() + "," +
                aoLan.getZydm() + "," +
                aoLan.getHkqzlxdm() + "," +
                aoLan.getHkqzszddm() + "," +
                aoLan.getLqyxdm() + ")";
        return aoLanStr;
    }


}
